-- SQL Server DDL to create all tables and foreign keys for ChatWithMultAI
-- Run this once against your database. Adjust schema/database name if needed.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

-- Users
CREATE TABLE dbo.Users
(
    UserId       UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Users_UserId DEFAULT NEWSEQUENTIALID(),
    DisplayName  NVARCHAR(100)    NOT NULL,
    CreatedAt    DATETIME2(7)     NOT NULL CONSTRAINT DF_Users_CreatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserId)
);
GO

-- Groups
CREATE TABLE dbo.[Groups]
(
    GroupId    UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Groups_GroupId DEFAULT NEWSEQUENTIALID(),
    [Name]     NVARCHAR(100)    NOT NULL,
    CreatedAt  DATETIME2(7)     NOT NULL CONSTRAINT DF_Groups_CreatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (GroupId)
);
GO

-- Group members (many-to-many Users <-> Groups)
CREATE TABLE dbo.GroupMembers
(
    GroupId   UNIQUEIDENTIFIER NOT NULL,
    UserId    UNIQUEIDENTIFIER NOT NULL,
    JoinedAt  DATETIME2(7)     NOT NULL CONSTRAINT DF_GroupMembers_JoinedAt DEFAULT SYSUTCDATETIME(),
    [Role]    NVARCHAR(32)     NULL,
    CONSTRAINT PK_GroupMembers PRIMARY KEY CLUSTERED (GroupId, UserId),
    CONSTRAINT FK_GroupMembers_Groups_GroupId FOREIGN KEY (GroupId) REFERENCES dbo.[Groups](GroupId) ON DELETE CASCADE,
    CONSTRAINT FK_GroupMembers_Users_UserId  FOREIGN KEY (UserId)  REFERENCES dbo.Users(UserId)  ON DELETE CASCADE
);
GO

CREATE NONCLUSTERED INDEX IX_GroupMembers_UserId ON dbo.GroupMembers(UserId);
GO

-- Messages
-- MessageKind: 0=Text, 1=System, 2=Agent
CREATE TABLE dbo.Messages
(
    MessageId   UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Messages_MessageId DEFAULT NEWSEQUENTIALID(),
    FromUserId  UNIQUEIDENTIFIER NOT NULL,
    ToUserId    UNIQUEIDENTIFIER NULL,
    ToGroupId   UNIQUEIDENTIFIER NULL,
    [Kind]      TINYINT          NOT NULL CONSTRAINT DF_Messages_Kind DEFAULT(0),
    [Content]   NVARCHAR(MAX)    NOT NULL,
    SentAt      DATETIME2(7)     NOT NULL CONSTRAINT DF_Messages_SentAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Messages PRIMARY KEY CLUSTERED (MessageId),
    CONSTRAINT CK_Messages_Kind CHECK ([Kind] IN (0,1,2)),
    -- exactly one of ToUserId or ToGroupId must be NOT NULL
    CONSTRAINT CK_Messages_EitherUserOrGroup CHECK ((CASE WHEN ToUserId  IS NULL THEN 0 ELSE 1 END) +
                                                    (CASE WHEN ToGroupId IS NULL THEN 0 ELSE 1 END) = 1),
    CONSTRAINT FK_Messages_FromUser FOREIGN KEY (FromUserId) REFERENCES dbo.Users(UserId),
    CONSTRAINT FK_Messages_ToUser   FOREIGN KEY (ToUserId)   REFERENCES dbo.Users(UserId),
    CONSTRAINT FK_Messages_ToGroup  FOREIGN KEY (ToGroupId)  REFERENCES dbo.[Groups](GroupId)
);
GO

CREATE NONCLUSTERED INDEX IX_Messages_ToUser_SentAt  ON dbo.Messages(ToUserId, SentAt)  WHERE ToUserId IS NOT NULL;
CREATE NONCLUSTERED INDEX IX_Messages_ToGroup_SentAt ON dbo.Messages(ToGroupId, SentAt) WHERE ToGroupId IS NOT NULL;
CREATE NONCLUSTERED INDEX IX_Messages_FromUser_SentAt ON dbo.Messages(FromUserId, SentAt);
GO

-- Agent Profiles (tamagochi-like progression)
CREATE TABLE dbo.AgentProfiles
(
    OwnerUserId UNIQUEIDENTIFIER NOT NULL,
    [Name]      NVARCHAR(100)    NOT NULL CONSTRAINT DF_AgentProfiles_Name DEFAULT(N'MyAgent'),
    Persona     NVARCHAR(MAX)    NULL,
    [Level]     INT              NOT NULL CONSTRAINT DF_AgentProfiles_Level DEFAULT(1),
    Xp          BIGINT           NOT NULL CONSTRAINT DF_AgentProfiles_Xp DEFAULT(0),
    ValueScore  FLOAT            NOT NULL CONSTRAINT DF_AgentProfiles_ValueScore DEFAULT(0),
    HonorScore  FLOAT            NOT NULL CONSTRAINT DF_AgentProfiles_HonorScore DEFAULT(0),
    SocialStatus NVARCHAR(32)    NOT NULL CONSTRAINT DF_AgentProfiles_SocialStatus DEFAULT(N'Normal'),
    CONSTRAINT PK_AgentProfiles PRIMARY KEY CLUSTERED (OwnerUserId),
    CONSTRAINT FK_AgentProfiles_OwnerUser FOREIGN KEY (OwnerUserId) REFERENCES dbo.Users(UserId) ON DELETE CASCADE
);
GO

-- Agent settings key-value
CREATE TABLE dbo.AgentSettings
(
    OwnerUserId UNIQUEIDENTIFIER NOT NULL,
    [Key]       NVARCHAR(100)    NOT NULL,
    [Value]     NVARCHAR(MAX)    NULL,
    CONSTRAINT PK_AgentSettings PRIMARY KEY CLUSTERED (OwnerUserId, [Key]),
    CONSTRAINT FK_AgentSettings_Profile FOREIGN KEY (OwnerUserId) REFERENCES dbo.AgentProfiles(OwnerUserId) ON DELETE CASCADE
);
GO

-- Optional: basic uniqueness to avoid duplicate group names per owner could be added later if ownership is modeled.
-- Note: Deleting Users referenced by Messages will fail due to FKs; prefer soft-delete or archival strategy.
